OpenEdge Data Management:<br />DataServer for ORACLE Record locking

OpenEdge applications rely on the ORACLE RDBMS to handle all record locking for the target ORACLE database. OpenEdge locks do not apply to your ORACLE database. Table 2–8 compares OpenEdge locks to their ORACLE equivalents.

Table 2–8: Progress 4GL and ORACLE locks
OpenEdge lock	ORACLE lock	Transaction processing option lock¹
`NO-LOCK`	None	None
`SHARE-LOCK`	None	None
`EXCLUSIVE-LOCK`	`SHARE UPDATE`(row-level)	`SHARE UPDATE`(row-level)
`EXCLUSIVE-LOCK` . . . `UPDATE`	`SHARE UPDATE` (row-level) . . `EXCLUSIVE` (table-level)	`SHARE UPDATE`(row-level) ... `ROW EXCLUSIVE` (row-level)

¹The ORACLE Transaction Processing option provides a low-level locking manager.

In applications that use the DataServer, locks occur as a result of Progress 4GL statements that the DataServer translates into SQL statements and sends to the ORACLE RDBMS.

Table 2–9 shows examples of Progress 4GL statements, the SQL statements they generate, and the resulting ORACLE locks in an ORACLE database. The examples assume the default is SHARE-LOCK. The notes that follow the table help explain the locking behavior.

Table 2–9: ORACLE locking
Progress 4GL statement	SQL statements generated	ORACLE locks
FIND customer.	SELECT . . . FROM customer;	None
FIND customer EXCLUSIVE-LOCK.	SELECT . . . FROM customer FOR UPDATE;	Share Update
FIND customer. . . . UPDATE customer.¹	SELECT . . . FROM customer SELECT . . . FROM customer FOR UPDATE; Compares records UPDATE customer . . . ;¹	None Share Update {Row} Exclusive²

Table 2–9: ORACLE locking

Progress 4GL statement

SQL statements generated

ORACLE
locks

FIND customer.

SELECT . . . FROM 
customer;

None

FIND customer 
EXCLUSIVE-LOCK.

SELECT . . . FROM 
customer
  FOR UPDATE;

Share Update

FIND customer.
  .
  .
  .



UPDATE customer.¹

SELECT . . . FROM 
customer
SELECT . . . FROM 
customer
  FOR UPDATE;

Compares records
UPDATE customer . . . ;¹

None

Share Update



{Row} Exclusive²

¹When the Progress 4GL encounters an UPDATE statement that involves an ORACLE database, it uses a FIND . . . EXCLUSIVE-LOCK statement to check whether the record referenced by the UPDATE statement is already locked.

If the record in the buffer is locked, the Progress 4GL starts the UPDATE. If not, it immediately issues an SQL SELECT . . . FOR UPDATE statement to determine whether the value in the buffer is the same as the value in the database. This statement also locks the record. If the values are different, the Progress 4GL returns a run-time error. When the
SELECT . . . FOR UPDATE statement completes successfully, the UPDATE starts.

When the Progress 4GL UPDATE completes, the Progress 4GL generates an SQL UPDATE statement that performs the actual change to the ORACLE database. For example, if you have to retrieve a record for a subsequent update, use the EXCLUSIVE-LOCK modifier with the FIND statement to avoid the second SELECT . . . FOR UPDATE operation.

NOTE: The last Progress 4GL statement in the table is an example of a lock upgrade.

²If you use ORACLE with the Transaction Processing option, the result is a Row Exclusive Lock. Without Transaction Processing, the result is a table-level Exclusive Lock.

The Progress 4GL and ORACLE release locks at different points in a transaction. When an application issues an UPDATE, the Progress 4GL releases the lock once the new data is input. ORACLE does not release the lock until the application issues a COMMIT or ROLLBACK. The Progress 4GL allows you to hold a lock outside of a transaction or beyond a transaction’s scope, but ORACLE always releases all locks at the end of a transaction.